package com.to8to.autotask;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.elasticsearch.common.collect.Lists;
import org.elasticsearch.common.collect.Maps;

import com.to8to.commons.utils.StringUtil;

/**
 * FindDiffBetweenDB.java
 * 作 者：atom.liu
 * 版 本：v2.0
 * 日 期：2015年1月13日
 * 描 述：找出1.57和2.11环境中表字段的不一致情况
 */
public class FindDiffBetweenDB
{
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connect_1_57 =
            DriverManager.getConnection("jdbc:mysql://192.168.1.57:3306/crm211?characterEncoding=utf-8&allowMultiQueries=true", "crm", "123456");
        Connection connect_2_11 =
            DriverManager.getConnection("jdbc:mysql://192.168.1.57:3306/ls_test?characterEncoding=utf-8&allowMultiQueries=true", "crm", "123456");

        List<String> tableList57 = Lists.newArrayList();
        List<String> tableList11 = Lists.newArrayList();

        {
            Statement stat = connect_1_57.createStatement();
            ResultSet rs = stat.executeQuery("SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'crm211'");
            while (rs.next())
                tableList57.add(rs.getString(1));
            releaseSource(rs, stat);
        }

        {
            Statement stat = connect_2_11.createStatement();
            ResultSet rs = stat.executeQuery("SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'ls_test'");
            while (rs.next())
                tableList11.add(rs.getString(1));
            releaseSource(rs, stat);
        }

        //Map<tableName: Map<columnName:columnType>>
        Map<String, Map<String, String>> infoMap57 = Maps.newHashMap();
        Map<String, Map<String, String>> infoMap11 = Maps.newHashMap();
        for (String tableName57 : tableList57)
        {
            if (!tableList11.contains(tableName57))
            {
                System.out.println(String.format("1.57中  表 【 %s 】在2.11中不存在", tableName57));
                continue;
            }

            {
                Map<String, String> columnMap = Maps.newHashMap();
                infoMap57.put(tableName57, columnMap);

                String sql =
                    String.format("SELECT COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE table_name = '%s' AND table_schema = '%s'", tableName57, "crm211");
                Statement stat = connect_1_57.createStatement();
                ResultSet rs = stat.executeQuery(sql);
                while (rs.next())
                    columnMap.put(rs.getString("COLUMN_NAME"), rs.getString("COLUMN_TYPE"));
                releaseSource(rs, stat);
            }

            {
                Map<String, String> columnMap = Maps.newHashMap();
                infoMap11.put(tableName57, columnMap);
                String sql =
                    String.format("SELECT COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE table_name = '%s' AND table_schema = '%s'", tableName57, "ls_test");
                Statement stat = connect_2_11.createStatement();
                ResultSet rs = stat.executeQuery(sql);
                while (rs.next())
                    columnMap.put(rs.getString("COLUMN_NAME"), rs.getString("COLUMN_TYPE"));
                releaseSource(rs, stat);
            }
        }

        System.out.println("=================================================");
        for (String tableName11 : tableList11)
        {
            if (!tableList57.contains(tableName11))
            {
                //System.out.println(String.format("2.11中  表 【 %s 】在1.57中不存在", tableName11));
                continue;
            }
        }
        System.out.println("=================================================");

        for (Map.Entry<String, Map<String, String>> tableEntry : infoMap57.entrySet())
        {
            String tableName = tableEntry.getKey();
            Map<String, String> columnMap57 = tableEntry.getValue();
            Map<String, String> columnMap11 = infoMap11.get(tableName);
            for (Map.Entry<String, String> columnEntry : columnMap57.entrySet())
            {
                String columnName = columnEntry.getKey();
                String columnType = columnEntry.getValue();
                String columnType11 = columnMap11.get(columnName);

                if (StringUtil.isEmpty(columnType11))
                {
                    System.out.println(String.format("##1.57表 【 %s 】字段【 %s 】在2.11中不存在", tableName, columnName));
                    continue;
                }

                if (!columnType11.equals(columnType))
                {
                    System.out.println(String.format("1.57 表【 %s 】 字段【 %s 】类型为 【 %s 】，而2.11中该字段类型为【 %s 】", tableName, columnName, columnType, columnType11));
                    continue;
                }
            }
        }

        System.out.println("=================================================");

        for (Map.Entry<String, Map<String, String>> tableEntry : infoMap11.entrySet())
        {
            String tableName = tableEntry.getKey();
            Map<String, String> columnMap11 = tableEntry.getValue();
            Map<String, String> columnMap57 = infoMap57.get(tableName);

            for (Map.Entry<String, String> columnEntry : columnMap11.entrySet())
            {
                String columnName = columnEntry.getKey();
                //String columnType = columnEntry.getValue();
                String columnType57 = columnMap57.get(columnName);

                if (StringUtil.isEmpty(columnType57))
                {
                    System.out.println(String.format("##2.11表 【 %s 】字段【 %s 】在1.57中不存在", tableName, columnName));
                    continue;
                }
            }
        }

        connect_1_57.close();
        connect_2_11.close();
    }

    private static void releaseSource(ResultSet rs, Statement stat) throws SQLException
    {
        stat.close();
        rs.close();
    }
}
